USE [Test_Events]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetIncidentsByCompanyIds]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spGetIncidentsByCompanyIds]
GO

CREATE PROCEDURE [dbo].[spGetIncidentsByCompanyIds]
	@CompanyIds NVARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @XML XML
	SET @XML = CONVERT(XML, @CompanyIds, 1)

	DECLARE @SelectedCompanies TABLE
	(
		Id uniqueidentifier
	)
	INSERT INTO @SelectedCompanies
	SELECT TID.id.value('.','uniqueidentifier') AS Id
	FROM @XML.nodes('/CompanyIds') AS T(c) outer apply T.c.nodes('Id') as TID(id)

	SELECT	Incidents.Id,
			Incidents.MainCompanyId AS CompanyId,
			EventIndicatorIncidents.EventIndicatorId,
			Incidents.IncidentId,
			Incidents.Type, 
			Incidents.Name AS Name, 
			Incidents.Summary AS Summary, 
			Incidents.Analysis AS Analysis, 
			PredictionTags.Name AS MainTag,
			Incidents.Source, 
			Incidents.IncidentDate AS IncidentDate, 
			Locations.Address AS LocationAddress,
			CASE WHEN Locations.Latitude IS NULL THEN 0 ELSE Locations.Latitude END AS LocationLatitude,
			CASE WHEN Locations.Longitude IS NULL THEN 0 ELSE Locations.Longitude END  AS LocationLongitude,
			ROUND(Incidents.ImpactValue/10, 0) AS ImpactScore, 
			ROUND(Incidents.ReputationalRiskValue/10, 0) AS RiskScore,
			Incidents.LastUpdated
	FROM	dbo.EventIndicatorIncidents AS EventIndicatorIncidents 
	INNER JOIN dbo.Incidents AS Incidents ON EventIndicatorIncidents.IncidentId = Incidents.Id 
	INNER JOIN dbo.NewsArticles AS NewsArticles ON Incidents.NewsArticleId = NewsArticles.Id 
	INNER JOIN dbo.PredictionTags AS PredictionTags ON Incidents.MainTagId = PredictionTags.Id 
	INNER JOIN dbo.Locations AS Locations ON Incidents.LocationId = Locations.Id 
    INNER JOIN dbo.EventIndicators ON EventIndicatorIncidents.EventIndicatorId = dbo.EventIndicators.Id 
    INNER JOIN dbo.EventIndicatorCompanyProfiles ON dbo.EventIndicators.CompanyProfileId = dbo.EventIndicatorCompanyProfiles.Id
	INNER JOIN @SelectedCompanies AS SelectedCompanies ON SelectedCompanies.Id = EventIndicatorCompanyProfiles.CompanyId
	WHERE  (Incidents.Status = 3) AND (dbo.EventIndicatorCompanyProfiles.Status = 0)
END
GO


